
-- ============
-- drop tables
IF OBJECT_ID('ssf.ptx_task_dependency') IS NOT NULL
	DROP TABLE ssf.ptx_task_dependency
GO

IF OBJECT_ID('ssf.ptx_task') IS NOT NULL
	DROP TABLE ssf.ptx_task
GO

IF OBJECT_ID('ssf.ptx_task_group') IS NOT NULL
	DROP TABLE ssf.ptx_task_group
GO
-- ============

-- ============
-- create tables
CREATE TABLE ssf.ptx_task_group (
	task_group_id INT IDENTITY(1, 1) NOT NULL
	, group_type NVARCHAR(128) DEFAULT N'DEFAULT' NOT NULL
	, group_name NVARCHAR(128) NOT NULL
	, group_description NVARCHAR(2048) NULL
	, insert_date SMALLDATETIME DEFAULT GETDATE() NOT NULL
)
GO

CREATE TABLE ssf.ptx_task (
	task_id INT IDENTITY(1, 1) NOT NULL
	, task_group_id INT NOT NULL
	, task_database_name NVARCHAR(128) NULL
	, task_procedure_name NVARCHAR(256) NOT NULL
	, task_parameter_list NVARCHAR(MAX) NULL
	, priority INT DEFAULT 1 NOT NULL
	, semaphore_name NVARCHAR(128) NULL
	, active_fl BIT DEFAULT 1 NOT NULL
	, insert_date SMALLDATETIME DEFAULT GETDATE() NOT NULL
)
GO

CREATE TABLE ssf.ptx_task_dependency (
	task_dependency_id INT IDENTITY(1, 1) NOT NULL
	, task_id INT NOT NULL
	, depends_on_task_id INT NOT NULL
	, active_fl BIT DEFAULT 1 NOT NULL
	, insert_date SMALLDATETIME DEFAULT GETDATE() NOT NULL
)
GO
-- ============

-- ============
-- create primary keys
ALTER TABLE ssf.ptx_task_group
ADD CONSTRAINT PK_ptx_task_group
PRIMARY KEY CLUSTERED (task_group_id)
GO

ALTER TABLE ssf.ptx_task
ADD CONSTRAINT PK_ptx_task
PRIMARY KEY CLUSTERED (task_id)
GO

ALTER TABLE ssf.ptx_task_dependency
ADD CONSTRAINT PK_ptx_task_dependency
PRIMARY KEY CLUSTERED (task_dependency_id)
GO
-- ============

-- ============
-- create indexes
ALTER TABLE ssf.ptx_task_group
ADD CONSTRAINT UQ_ptx_task_group
UNIQUE (group_type, group_name)
GO

ALTER TABLE ssf.ptx_task_dependency
ADD CONSTRAINT UQ_ptx_task_dependency
UNIQUE (task_id, depends_on_task_id)
GO

CREATE NONCLUSTERED INDEX ndx_group_id
ON ssf.ptx_task (task_group_id)
GO
-- ============

-- ============
-- create foreign keys
ALTER TABLE ssf.ptx_task
ADD CONSTRAINT FK_ptx_task_task_group_id
FOREIGN KEY (task_group_id)
REFERENCES ssf.ptx_task_group (task_group_id)
GO

ALTER TABLE ssf.ptx_task_dependency
ADD CONSTRAINT FK_ptx_task_dependency_task_id
FOREIGN KEY (task_id)
REFERENCES ssf.ptx_task (task_id)
GO

ALTER TABLE ssf.ptx_task_dependency
ADD CONSTRAINT FK_ptx_task_dependency_depends_on_task_id
FOREIGN KEY (depends_on_task_id)
REFERENCES ssf.ptx_task (task_id)
GO
-- ============

-- ============
-- create check constraints
ALTER TABLE ssf.ptx_task
ADD CONSTRAINT CK_priority
CHECK (priority >= 1)
GO

ALTER TABLE ssf.ptx_task_dependency
ADD CONSTRAINT CK_depends_on_task_id
CHECK (depends_on_task_id <> task_id)
GO
-- ============
